#include "ModelService.h"
#include "../db/DBPoll.h"
#include "../util/DateTime.h"
#include "../util/TextUtil.h"
#include "../util/FileUtil.h"
#include "../util/SystemHelper.h"

// 数据表名称
#define SQL_DB_NAME_MODEL	"t_model"

// 建表语句
#define SQL_CREATE_MODEL \
"CREATE TABLE IF NOT EXISTS `t_model` ("\
"`id` VARCHAR(64) NOT NULL comment '模型编码',"\
"`name` VARCHAR(32) default NULL comment '模型名称',"\
"`description` VARCHAR(64) default NULL comment '模型描述',"\
"`path` VARCHAR(255) default NULL comment '模型路径',"\
"`cls` TEXT comment '模型分类(注意顺序)',"\
"`normal` TEXT comment '正常分类(逗号隔开)',"\
"`alarm` TEXT comment '异常分类(逗号隔开)',"\
"`width` INT default 640 comment '训练宽度',"\
"`height` INT default 640 comment '训练高度',"\
"`enabled` TINYINT(1) default 1 comment '启用状态',"\
"`createTime` DATETIME default NULL comment '添加时间',"\
"`reserver1` VARCHAR(255) default NULL comment '保留字段1',"\
"`reserver2` VARCHAR(255) default NULL comment '保留字段2',"\
"primary key(`id`)"\
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统模型'"

// SQLite建表
#define SQL_CREATE_MODEL2	\
"CREATE TABLE IF NOT EXISTS `t_model` ("\
"`id` VARCHAR(64) NOT NULL,"\
"`name` VARCHAR(32) default NULL,"\
"`description` VARCHAR(64) default NULL,"\
"`path` VARCHAR(255) default NULL,"\
"`cls` TEXT,"\
"`normal` TEXT,"\
"`alarm` TEXT,"\
"`width` INT default 640,"\
"`height` INT default 640,"\
"`enabled` TINYINT(1) default 1,"\
"`createTime` DATETIME default NULL,"\
"`reserver1` VARCHAR(255) default NULL,"\
"`reserver2` VARCHAR(255) default NULL,"\
"primary key(`id`)"\
")"

// 新增语句
#define SQL_INSERT_MODEL \
"insert into t_model(`id`, `name`, `description`, `path`, `cls`, `normal`,"\
" `alarm`, `width`, `height`, `enabled`,`createTime`) "\
" values('%s', '%s', '%s', '%s', '%s', '%s', '%s', %d, %d, %d, '%s')"

// 删除语句					  
#define SQL_DELETE_MODEL \
"delete from t_model where `id`='%s'"

// 查询语句					  
#define SQL_QUERY_MODEL \
"select * from t_model where `id`='%s'"

// 查询数量
#define SQL_QUERY_MODEL_COUNT \
"select count(*) from t_model where 1=1"

// 是否存在
#define SQL_QUERY_MODEL_EXIST \
"select count(*) from t_model where `id`='%s'"

// 分页查询					  
#define SQL_QUERY_MODEL_PAGE \
"select * from t_model where 1=1"

// 更新语句					  
#define SQL_UPDATE_MODEL \
"update t_model set `name`='%s',`description`='%s'," \
"`cls`='%s', `normal`='%s', `alarm`='%s',`width`=%d,"\
"`height`=%d, `enabled`=%d, `createTime`='%s'" \
" where `id`='%s'"


ModelService::ModelService()
{
}

ModelService::~ModelService()
{
}

bool ModelService::Exist()
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		bool ret = db->IsTableExist(SQL_DB_NAME_MODEL);
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool ModelService::Create()
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = db->GetDbType() == 0 ? SQL_CREATE_MODEL : SQL_CREATE_MODEL2;
		sql = TextUtil::get_mutable_instance().Ascii2Utf8(sql.c_str());
		bool ret = db->CreateTable(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool ModelService::Add(ModelInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_INSERT_MODEL)
			% item.GetId() % item.GetName()
			% item.GetDescription() % item.GetPath()
			% item.GetCls() % item.GetNormal()
			% item.GetAlarm() 
			% item.GetWidth() 
			% item.GetHeight()
			% item.GetEnabled()
			% item.GetCreateTime().ToNormalString());

		uint64_t id = db->Insert(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		if (0xffffffff == id)
		{
			return false;
		}
		return true;
	}
	return false;
}

bool ModelService::Delete(ModelInfo& item, bool delFromDB)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		bool ret = false;
		if (delFromDB)
		{
			std::string sql = boost::str(boost::format(SQL_DELETE_MODEL) % item.GetId());
			ret = db->Delete(sql.c_str());
		}

		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

// 查询数量
int ModelService::Count()
{
	int count = 0;
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL_COUNT));
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			if (db->FetchNextRow())
			{
				char* v = db->GetColValue(0);
				count = std::stoi(v);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
	}
	return count;
}

bool ModelService::Exist(std::string id, bool& result)
{
	result = false;
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL_EXIST) % id);
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			if (db->FetchNextRow())
			{
				char* v = db->GetColValue(0);
				int count = std::stoi(v);
				result = (count > 0);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

int ModelService::Count(std::string name, std::string enabled)
{
	int count = 0;
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL_COUNT));
		if (!name.empty())
		{
			sql += boost::str(boost::format(" and `name` like '%%%s%%'") % name);
		}
		if (!enabled.empty())
		{
			sql += boost::str(boost::format(" and `enabled`=%s") % enabled);
		}
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			if (db->FetchNextRow())
			{
				char* v = db->GetColValue(0);
				count = std::stoi(v);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
	}
	return count;
}

// 查找记录
bool ModelService::FindPage(int pageIndex, int pageSize, std::vector<ModelInfo>& list)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL_PAGE));
		sql += boost::str(boost::format(" limit %d, %d") % pageIndex % pageSize);
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			while (db->FetchNextRow())
			{
				ModelInfo item;
				ToRecord(db, item);
				list.push_back(item);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

// 查找记录
bool ModelService::FindPage(int pageIndex, int pageSize, std::string enabled, std::vector<ModelInfo>& list)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL_PAGE));
		if (!enabled.empty())
		{
			sql += boost::str(boost::format(" and `enabled`=%s") % enabled);
		}
		sql += boost::str(boost::format(" limit %d, %d") % pageIndex % pageSize);
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			while (db->FetchNextRow())
			{
				ModelInfo item;
				ToRecord(db, item);
				list.push_back(item);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool ModelService::FindPage(int pageIndex, int pageSize, std::string name, std::string enabled, std::vector<ModelInfo>& list)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL_PAGE));
		if (!name.empty())
		{
			sql += boost::str(boost::format(" and `name` like '%%%s%%'") % name);
		}
		if (!enabled.empty())
		{
			sql += boost::str(boost::format(" and `enabled`=%s") % enabled);
		}
		sql += boost::str(boost::format(" limit %d, %d") % pageIndex % pageSize);
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			while (db->FetchNextRow())
			{
				ModelInfo item;
				ToRecord(db, item);
				list.push_back(item);
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool ModelService::Find(ModelInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_QUERY_MODEL) % item.GetId());
		bool ret = db->Query(sql.c_str());
		if (ret)
		{
			ret = false;
			if (db->FetchNextRow())
			{
				ToRecord(db, item);
				ret = true;
			}
			db->FreeQueryResult();
		}
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}

bool ModelService::Update(ModelInfo& item)
{
	IDbBasePtr db = DBPoll::get_mutable_instance().Pop();
	if (db)
	{
		std::string sql = boost::str(boost::format(SQL_UPDATE_MODEL)
			% item.GetName() % item.GetDescription()
			% item.GetCls() % item.GetNormal()
			% item.GetAlarm() 
			% item.GetWidth()
			% item.GetHeight()
			% item.GetEnabled()
			% item.GetCreateTime().ToNormalString()
			% item.GetId());
		bool ret = db->Update(sql.c_str());
		DBPoll::get_mutable_instance().Push(db);
		return ret;
	}
	return false;
}


void ModelService::ToRecord(IDbBasePtr db, ModelInfo& item)
{
	std::string id = "";
	if (db->GetColValue(0))
	{
		id = db->GetColValue(0);
	}
	std::string name = "";
	if (db->GetColValue(1))
	{
		name = db->GetColValue(1);
	}
	std::string description = "";
	if (db->GetColValue(2))
	{
		description = db->GetColValue(2);
	}
	std::string path = "";
	if (db->GetColValue(3))
	{
		path = db->GetColValue(3);
		// http://192.168.8.19:5700/file/download?name=1752229121892683776.pt
		if (TextUtil::get_mutable_instance().StartWith(path, "http://"))
		{
			int pos = path.rfind("name=");
			if (pos != -1)
			{
				// pt模型转onnx模型
				std::string name = path.substr(pos + strlen("name="));
				if (name.find(".pt") != -1)
				{
					name.replace(name.find(".pt"), 3, ".onnx");
				}
				// 从model文件夹查找
				path = SystemHelper::get_mutable_instance().GetFileSavePath(name);
				if (!FileUtil::Exists(path))
				{
					path = FileUtil::GetAppPath("/") + name;
				}
				// 从bin目录中查找
				if (path.find(".pt") != -1)
				{
					path.replace(path.find(".pt"), 3, ".onnx");
				}
			}
		}
	}
	std::string cls = "";
	if (db->GetColValue(4))
	{
		cls = db->GetColValue(4);
	}
	std::string normal = "";
	if (db->GetColValue(5))
	{
		normal = db->GetColValue(5);
	}
	std::string alarm = "";
	if (db->GetColValue(6))
	{
		alarm = db->GetColValue(6);
	}
	int width = 640;
	if (db->GetColValue(7))
	{
		width = atoi(db->GetColValue(7));
	}
	int height = 640;
	if (db->GetColValue(8))
	{
		height = atoi(db->GetColValue(8));
	}
	int enabled = 1;
	if (db->GetColValue(9))
	{
		enabled = atoi(db->GetColValue(9));
	}
	std::string createTime = "";
	if (db->GetColValue(10))
	{
		createTime = db->GetColValue(10);
	}

	item.SetId(id);
	item.SetName(name);
	item.SetDescription(description);
	item.SetPath(path);
	item.SetCls(cls);
	item.SetNormal(normal);
	item.SetAlarm(alarm);
	item.SetWidth(width);
	item.SetHeight(height);
	item.SetEnabled(enabled);

	DateTime create_time;
	create_time.From(createTime);
	item.SetCreateTime(create_time);
}
